﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml;

namespace MRSite.Entity
{
    public class SqlOp
    {
        public static string CONNDB = ConfigurationManager.ConnectionStrings["SQLConn"].ToString();

        public static DataSet ExecuteQuery(string connString, CommandType cmdType, string cmdText, out int RecordCount, out int PageCount, params SqlParameter[] cmdParms)
        {
            DataSet mySet = new DataSet();
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection myConnection = new SqlConnection(connString))
            {
                PrepareCommand(cmd, myConnection, null, cmdType, cmdText, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(mySet);
                myConnection.Close();
            }
            RecordCount = (int)cmd.Parameters[7].Value;
            PageCount = (int)cmd.Parameters[8].Value;
            return mySet;
        }

        /// <summary>
        /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
        /// to the provided command
        /// </summary>
        /// <param name="command">The SqlCommand to be prepared</param>
        /// <param name="connection">A valid SqlConnection, on which to execute this command</param>
        /// <param name="transaction">A valid SqlTransaction, or 'null'</param>
        /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">The stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
        /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
               
                connection.Open();
            }
            else
            {
               
            }

            // Associate the connection with the command
            command.Connection = connection;

            command.CommandTimeout = 30;//命令超时时间 

            // Set the command text (stored procedure name or SQL statement)
            command.CommandText = commandText;

            // If we were provided a transaction, assign it
            if (transaction != null)
            {
                if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // Set the command type
            command.CommandType = commandType;

            // Attach the command parameters if they are provided
            if (commandParameters != null)
            {
                AttachParameters(command, commandParameters);
            }
            return;
        }

        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                         p.Direction == ParameterDirection.Input) &&
                         (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }


        public static DataSet GetListbyPage(string tbName, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string FieldOrder, string strWhere, out  int RecordCount, out int PageCount)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@tbname", SqlDbType.VarChar,50),
                new SqlParameter("@FieldKey", SqlDbType.VarChar,50),
                new SqlParameter("@PageCurrent", SqlDbType.Int,4),
                new SqlParameter("@PageSize", SqlDbType.Int,4),
                new SqlParameter("@FieldShow", SqlDbType.VarChar,50),
                new SqlParameter("@FieldOrder", SqlDbType.VarChar,50),
                new SqlParameter("@Where", SqlDbType.VarChar,50),
                new SqlParameter("@RecordCount", SqlDbType.Int,4),
                new SqlParameter("@PageCount", SqlDbType.Int,4)
            };
            parameters[0].Value = tbName;
            parameters[1].Value = FieldKey;
            parameters[2].Value = PageCurrent;
            parameters[3].Value = PageSize;
            parameters[4].Value = FieldShow;
            parameters[5].Value = FieldOrder;
            parameters[6].Value = strWhere;
            parameters[7].Direction = ParameterDirection.Output;
            parameters[8].Direction = ParameterDirection.Output;
            return SqlOp.ExecuteQuery(SqlOp.CONNDB, CommandType.StoredProcedure, "SP_PageListNew", out RecordCount, out PageCount, parameters);
        }
    }
 
}
